<?php
  function get_tsx_theo_ten($tentsx) {
  $query = db_select('dt_to_san_xuat', 'tsx');
  $query->fields('tsx', array('id', 'ma_to_san_xuat', 'ten_to_san_xuat'));
  $query->condition('tsx.ten_to_san_xuat', $tentsx, '=');
  $data_ten = '';
  $result = $query->execute();
  while ($rs = $result->fetchObject()) {
    $data_ten = $rs->ma_to_san_xuat;
  }
  return $data_ten;
}

  function get_tsx_autocomplete($string) {
    $query = db_select('dt_to_san_xuat', 'tsx')->extend('PagerDefault');
    $query->fields('tsx', array('ma_to_san_xuat', 'ten_to_san_xuat'));
    $query->condition(
      db_or()->
        condition('tsx.ten_to_san_xuat', '%' . db_like($string) . '%', 'LIKE')->
        condition('tsx.ma_to_san_xuat', $string, '=')->
        condition('tsx.ma_to_san_xuat', 'TSX_' . $string, '=')
    );
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ten_to_san_xuat . ' [' . $rs->ma_to_san_xuat . ']'] = $rs->ten_to_san_xuat . ' [' . $rs->ma_to_san_xuat . ']';
    }
    return $data;
  }

  function insert_to_san_xuat($uid, $ten_to_san_xuat, $ghi_chu) {
    $thoi_gian = time();
    $trang_thai = 1;
    $query = db_insert('dt_to_san_xuat');
    $arr = array(
      'uid' => $uid,
      'ma_to_san_xuat' => '',
      'ten_to_san_xuat' => $ten_to_san_xuat,
      'ghi_chu' => $ghi_chu,
      'trang_thai' => $trang_thai,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_to_san_xuat = $id;
    while (strlen($ma_to_san_xuat) < 3) {
      $ma_to_san_xuat = '0' . $ma_to_san_xuat;
    }
    $ma_to_san_xuat = 'TSX_' . $ma_to_san_xuat;
    db_update('dt_to_san_xuat')
      ->fields(array(
                    'ma_to_san_xuat' => $ma_to_san_xuat,
               ))
      ->condition('id', $id)
      ->execute();
  }

  function update_to_san_xuat($ten_to_san_xuat, $ghi_chu, $ma_to_san_xuat) {
    db_update('dt_to_san_xuat')
      ->fields(array(
                    'ten_to_san_xuat' => $ten_to_san_xuat,
                    'ghi_chu' => $ghi_chu,
               ))
      ->condition('ma_to_san_xuat', $ma_to_san_xuat)
      ->execute();
  }

  function get_to_san_xuat_theo_ma($ma_to_san_xuat) {
    $query = db_select('dt_to_san_xuat', 'tsx');
    $query->fields('tsx', array('id', 'ma_to_san_xuat', 'ten_to_san_xuat', 'ghi_chu'));
    $query->condition('tsx.ma_to_san_xuat', $ma_to_san_xuat, '=');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function get_to_san_xuat_theo_id($id) {
    $query = db_select('dt_to_san_xuat', 'tsx');
    $query->fields('tsx', array('id', 'ma_to_san_xuat', 'ten_to_san_xuat', 'ghi_chu'));
    $query->condition('tsx.id', $id, '=');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function get_to_san_xuat($trang_thai, $header = array()) {
    $query = db_select('dt_to_san_xuat', 'tsx')->extend('PagerDefault')->extend('TableSort');
    $query->fields('tsx', array('id', 'ma_to_san_xuat', 'ten_to_san_xuat', 'ghi_chu', 'thoi_gian'));
    $query->addExpression('(SELECT COUNT(nv.id) FROM {dt_nhan_vien} AS nv WHERE nv.id_tosanxuat = tsx.id AND nv.trang_thai = 1)', 'so_cong_nhan');
    $query->condition('tsx.trang_thai', $trang_thai);
    $query->orderByHeader($header);
    $result = $query->limit(20)->execute();
    $data = array();
    //    $params = $_GET;
    //    unset($params['q']);
    //    $str_param = '';
    //    if (!empty($params)) {
    //      foreach ($params as $key => $val) {
    //        $str_param .= '&' . $key . '=' . $val;
    //      }
    //    }
    $edit_perm = user_access('sua to san xuat');
    while ($rs = $result->fetchObject()) {
      //      if ($trang_thai) {
      //        $link = '<a href="' . base_path() . 'nha_cung_cap/trang_thai/ngung/' . $rs->ma_nha_cung_cap . '?destination=nha_cung_cap' . $str_param .'">Ngưng cộng tác</a>';
      //      }
      //      else {
      //        $link = '<a href="' . base_path() . 'nha_cung_cap/trang_thai/tiep_tuc/' . $rs->ma_nha_cung_cap . '?destination=nha_cung_cap/danh_sach_nha_cung_cap/ngung_cong_tac' . $str_param .'">Tiếp tục cộng tác</a>';
      //      }
      $data[] = array(
        'ma_to_san_xuat' => $rs->ma_to_san_xuat,
        'ten_to_san_xuat' => $rs->ten_to_san_xuat,
        'so_cong_nhan' => $rs->so_cong_nhan,
        'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'thao_tac' => ($edit_perm ? l('Sửa', 'to_san_xuat/sua_to_san_xuat/' . $rs->ma_to_san_xuat)
          : '') . dt_view_ghi_chu('tsx_' . $rs->id, 'Ghi chú tổ ' . $rs->ten_to_san_xuat, $rs->ghi_chu)
      );
    }
    return $data;
  }

  function get_to_san_xuat_ngay($time) {
    $data = array();
    $query = db_select('dt_to_san_xuat', 'tsx');
    $query->fields('tsx', array('ma_to_san_xuat', 'ten_to_san_xuat'));
    $query->condition('tsx.trang_thai', 1);
    $query->condition(
    db_or()->
      condition('tsx.ma_to_san_xuat', 'SX_VUNG', '=')->
      condition('tsx.ma_to_san_xuat', 'SX_DAY', '=')->
      condition('tsx.ma_to_san_xuat', 'SX_THAN', '=')->
      condition('tsx.ma_to_san_xuat', 'SX_THUT', '=')->
      condition('tsx.ma_to_san_xuat', 'DB', '=')->
      condition('tsx.ma_to_san_xuat', 'HT_DH', '=')->
      condition('tsx.ma_to_san_xuat', 'HT_LAU', '=')->
      condition('tsx.ma_to_san_xuat', 'HT_LR1', '=')->
      condition('tsx.ma_to_san_xuat', 'HT_LR2', '=')->
      condition('tsx.ma_to_san_xuat', 'HT_LUOT', '=')->
      condition('tsx.ma_to_san_xuat', 'HT_PK', '=')
      
  );
    $result = $query->orderBy('tsx.ma_to_san_xuat')->execute();
    $data_td = get_tien_do_san_xuat_ngay($time);
    while ($rs = $result->fetchObject()) {
      $chi_tieu = '';
      $so_cong_nhat = '';
      $ket_qua = '';
      $trang_thai = '';
      $ghi_chu = '';
      $id = '';
      if (isset($data_td[$rs->ma_to_san_xuat])) {
        $tien_do = $data_td[$rs->ma_to_san_xuat];
        $id = $tien_do->id;
        $chi_tieu = $tien_do->chi_tieu;
        if ($tien_do->trang_thai) {
          $so_cong_nhat = $tien_do->so_cong_nhat;
          $ket_qua = $tien_do->ket_qua;
          $trang_thai = $tien_do->trang_thai;
          $ghi_chu = dt_view_ghi_chu('tdsx_' . $tien_do->id, 'Ghi chú tiến độ ' . $rs->ten_to_san_xuat, $tien_do->ghi_chu, false);
        }
      }
      if (!$trang_thai && $chi_tieu) {
        //        $chi_tieu .= ' | <a class="sua_chi_tieu" href="javascript:;" onclick="sua_chi_tieu(\''.$rs->ma_to_san_xuat.'\', \''.$rs->ten_to_san_xuat.'\', '.$id.', '.$chi_tieu.')">Sửa</a>';
        $trang_thai = l('Nhập kết quả', 'to_san_xuat/tien_do_san_xuat/nhap_ket_qua/' . $id);
      }
      else {
        switch ($trang_thai) {
          case 1:
            $trang_thai = 'Không đạt';
            break;
          case 2:
            $trang_thai = 'Hoàn thành';
            break;
          case 3:
            $trang_thai = 'Vượt chỉ tiêu';
            break;
        }
      }
      if (!$chi_tieu) {
        //        $chi_tieu = '<a class="nhap_chi_tieu" href="javascript:;" onclick="nhap_chi_tieu(\''.$rs->ma_to_san_xuat.'\', \''.$rs->ten_to_san_xuat.'\')">Nhập chỉ tiêu</a>';
      }
      $data[] = array(
        'ten_to_san_xuat' => array('data' => $rs->ten_to_san_xuat),
        'cong_nhat' => array('data' => $so_cong_nhat),
        'chi_tieu' => array('data' => $chi_tieu),
        'ket_qua' => array('data' => $ket_qua),
        'trang_thai' => array('data' => $trang_thai),
        $ghi_chu
      );
    }
    return $data;
  }

  function get_tien_do_san_xuat_ngay($time, $id = 0) {
    $query = db_select('dt_tien_do_san_xuat', 'td');
    $query->fields('td', array('id', 'ma_to_san_xuat', 'chi_tieu', 'so_cong_nhat', 'hang_loi', 'ket_qua', 'trang_thai', 'ghi_chu'));
    $query->condition('td.ngay_thang', date('Y-m-d', $time));
    if ($id) {
      $query->condition('td.id', $id);
    }
    $result = $query->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ma_to_san_xuat] = $rs;
    }
    return $data;
  }

  function insert_tien_do_san_xuat_ngay($chi_tieu, $time, $ma_tsx = '') {
    global $user;
    $trang_thai = 0;
    $thoi_gian = time();
    if ($ma_tsx) {
      $query = db_insert('dt_tien_do_san_xuat');
      $arr = array(
        'uid' => $user->uid,
        'ma_to_san_xuat' => $ma_tsx,
        'ngay_thang' => date('Y-m-d', $time),
        'chi_tieu' => $chi_tieu,
        'trang_thai' => $trang_thai,
        'ghi_chu' => '',
        'thoi_gian' => $thoi_gian
      );
      $query->fields(array_keys($arr));
      $query->values($arr);
      $query->execute();
    }
    else {
      $query = db_select('dt_to_san_xuat', 'tsx');
      $query->fields('tsx', array('ma_to_san_xuat', 'ten_to_san_xuat'));
      $query->condition('tsx.trang_thai', 1);
      $result = $query->orderBy('tsx.ma_to_san_xuat')->execute();
      $data_td = array_keys(get_tien_do_san_xuat_ngay($time));
      $i = 0;
      while ($rs = $result->fetchObject()) {
        if (!in_array($rs->ma_to_san_xuat, $data_td)) {
          $arr = array(
            'uid' => $user->uid,
            'ma_to_san_xuat' => $rs->ma_to_san_xuat,
            'ngay_thang' => date('Y-m-d', $time),
            'chi_tieu' => $chi_tieu,
            'trang_thai' => $trang_thai,
            'ghi_chu' => '',
            'thoi_gian' => $thoi_gian
          );
          if (!$i) {
            $query = db_insert('dt_tien_do_san_xuat');
            $query->fields(array_keys($arr));
          }
          $query->values($arr);
          $i++;
        }
      }
      if ($i) {
        $query->execute();
      }
    }
  }

  function update_tien_do_san_xuat_ngay($chi_tieu, $time, $ma_tsx) {
    db_update('dt_tien_do_san_xuat')
      ->fields(array(
                    'chi_tieu' => $chi_tieu,
               ))
      ->condition('ma_to_san_xuat', $ma_tsx)
      ->condition('ngay_thang', date('Y-m-d', $time))
      ->execute();
  }

  function get_tien_do_san_xuat_ngay_theo_id($id_tien_do) {
    $query = db_select('dt_to_san_xuat', 'tsx');
    $query->innerJoin('dt_tien_do_san_xuat', 'td', 'td.ma_to_san_xuat = tsx.ma_to_san_xuat');
    $query->fields('tsx', array('ma_to_san_xuat', 'ten_to_san_xuat'));
    $query->fields('td', array('id', 'ngay_thang', 'chi_tieu', 'trang_thai', 'ghi_chu'));
    $query->addField('tsx', 'id', 'id_tsx');
    $query->addExpression('(SELECT COUNT(nv.id) FROM {dt_nhan_vien} AS nv WHERE nv.id_tosanxuat = tsx.id AND nv.trang_thai = 1)', 'so_cong_nhan');
    $query->condition('tsx.trang_thai', 1);
    $query->condition('td.id', $id_tien_do);
    return $query->execute()->fetchObject();
  }

  function insert_thuong_phat_to($type, $ma_to_san_xuat, $ngay_thang, $tien_phat_to_arr, $ly_do_phat_to_arr) {
    global $user;
    $query = db_insert('dt_thuong_phat_to_san_xuat');
    $i = 0;
    $thoi_gian = time();
    foreach ($tien_phat_to_arr as $key => $tien_phat) {
      $arr = array(
        'uid' => $user->uid,
        'type' => $type,
        'ma_to_san_xuat' => $ma_to_san_xuat,
        'ngay_thang' => $ngay_thang,
        'so_tien' => str_replace(',', '', $tien_phat_to_arr[$key]),
        'ly_do' => $ly_do_phat_to_arr[$key],
        'ma_bang_luong' => 0,
        'thoi_gian' => $thoi_gian
      );
      if (!$i) {
        $query->fields(array_keys($arr));
      }
      $query->values($arr);
      $i++;
    }
    $query->execute();
  }

  function get_id_phat_to_by_date($ngay_thang) {
    $query = db_select('dt_thuong_phat_to_san_xuat', 'tp');
    $query->fields('tp', array('id'));
    $query->condition('tp.ngay_thang', $ngay_thang);
    $result = $query->execute();
    $str_id = '';
    while ($rs = $result->fetchObject()) {
      if ($str_id) {
        $str_id .= ',';
      }
      $str_id .= $rs->id;
    }
    return $str_id;
  }

  function update_ket_qua_san_xuat_ngay($so_cong_nhat, $ket_qua, $so_hong, $thuc_te, $ghi_chu, $id_tien_do) {
    db_update('dt_tien_do_san_xuat')
      ->fields(array(
                    'so_cong_nhat' => $so_cong_nhat,
                    'trang_thai' => $ket_qua,
                    'hang_loi' => $so_hong,
                    'ket_qua' => $thuc_te,
                    'ghi_chu' => $ghi_chu,
               ))
      ->condition('id', $id_tien_do)
      ->execute();
  }

  function get_thong_ke_tien_do_san_xuat_ngay($start_month, $time) {
    $data = array();
    $data_td = get_tien_do_san_xuat_ngay($time);
    if (!empty($data_td)) {
      $dau_thang = date('Y-m-d', $start_month);
      $hien_tai = date('Y-m-d', $time);
      $type = 'phat';
      $query = db_select('dt_to_san_xuat', 'tsx');
      $query->condition('tsx.trang_thai', 1);
      $query->fields('tsx', array('ma_to_san_xuat', 'ten_to_san_xuat'));
      $query->addExpression('(SELECT SUM(td_1.ket_qua) FROM {dt_tien_do_san_xuat} AS td_1 WHERE td_1.ma_to_san_xuat = tsx.ma_to_san_xuat AND td_1.ngay_thang >= :dau_thang_1 AND td_1.ngay_thang <= :hien_tai_1 AND trang_thai <> 0)', 'tong_san_luong', array(':dau_thang_1' => $dau_thang, ':hien_tai_1' => $hien_tai));
      $query->addExpression('(SELECT SUM(td_2.hang_loi) FROM {dt_tien_do_san_xuat} AS td_2 WHERE td_2.ma_to_san_xuat = tsx.ma_to_san_xuat AND td_2.ngay_thang >= :dau_thang_2 AND td_2.ngay_thang <= :hien_tai_2 AND trang_thai <> 0)', 'tong_so_loi', array(':dau_thang_2' => $dau_thang, ':hien_tai_2' => $hien_tai));
      $query->addExpression('(SELECT COUNT(td_3.id) FROM {dt_tien_do_san_xuat} AS td_3 WHERE td_3.ma_to_san_xuat = tsx.ma_to_san_xuat AND td_3.ngay_thang >= :dau_thang_3 AND td_3.ngay_thang <= :hien_tai_3 AND trang_thai <> 0)', 'tong_so_ngay', array(':dau_thang_3' => $dau_thang, ':hien_tai_3' => $hien_tai));
      $query->addExpression('(SELECT SUM(tp_1.so_tien) FROM {dt_thuong_phat_to_san_xuat} AS tp_1 WHERE tp_1.ma_to_san_xuat = tsx.ma_to_san_xuat AND tp_1.ngay_thang >= :dau_thang_4 AND tp_1.ngay_thang <= :hien_tai_4 AND type = :type_1)', 'tong_phat_to', array(':dau_thang_4' => $dau_thang, ':hien_tai_4' => $hien_tai, ':type_1' => $type));
      $query->addExpression('(SELECT SUM(tp_2.so_tien) FROM {dt_thuong_phat_nhan_vien} AS tp_2 INNER JOIN {dt_nhan_vien} AS nv ON tp_2.ma_nhan_vien = nv.ma_nhan_vien WHERE nv.id_tosanxuat = tsx.id AND tp_2.ngay_thang >= :dau_thang_5 AND tp_2.ngay_thang <= :hien_tai_5 AND type = :type_2)', 'tong_phat_nv', array(':dau_thang_5' => $dau_thang, ':hien_tai_5' => $hien_tai, ':type_2' => $type));
      $result = $query->orderBy('tsx.id')->execute();
      while ($rs = $result->fetchObject()) {
        $rs->cong_nhat = '';
        $rs->chi_tieu = '';
        $rs->ket_qua = '';
        $rs->hang_loi = '';
        $rs->ghi_chu = '';
        if (isset($data_td[$rs->ma_to_san_xuat])) {
          $tien_do = $data_td[$rs->ma_to_san_xuat];
          $rs->chi_tieu = $tien_do->chi_tieu;
          $data['chi_tieu'] = $tien_do->chi_tieu;
          if ($data_td[$rs->ma_to_san_xuat]->trang_thai != 0) {
            $rs->cong_nhat = $tien_do->so_cong_nhat;
            $rs->ket_qua = $tien_do->ket_qua;
            $rs->hang_loi = $tien_do->hang_loi;
            $rs->ghi_chu = $tien_do->ghi_chu;
          }
        }
        $data['rows'][$rs->ma_to_san_xuat] = $rs;
      }
    }
    return $data;
  }

  function get_thuong_phat_to_san_xuat_ngay($type, $time) {
    $query = db_select('dt_thuong_phat_to_san_xuat', 'tp');
    $query->fields('tp', array('id', 'ma_to_san_xuat', 'so_tien', 'ly_do'));
    $query->condition('tp.type', $type);
    $query->condition('tp.ngay_thang', date('Y-m-d', $time));
    $result = $query->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ma_to_san_xuat][] = $rs;
    }
    return $data;
  }

  function get_thuong_phat_nhan_vien_ngay($type, $time) {
    $query = db_select('dt_thuong_phat_nhan_vien', 'tp');
    $query->innerJoin('dt_nhan_vien', 'nv', 'nv.ma_nhan_vien = tp.ma_nhan_vien');
    $query->innerJoin('dt_to_san_xuat', 'tsx', 'nv.id_tosanxuat = tsx.id');
    $query->fields('tp', array('id', 'ma_nhan_vien', 'so_tien', 'ly_do'));
    $query->fields('nv', array('ten_nhan_vien'));
    $query->fields('tsx', array('ma_to_san_xuat'));
    $query->condition('tp.type', $type);
    $query->condition('tp.ngay_thang', date('Y-m-d', $time));
    $result = $query->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ma_to_san_xuat][] = $rs;
    }
    return $data;
  }

  function get_thong_ke_tien_do_san_xuat_thang($start_month, $end_month) {
    $data = array();
    return $data;
  }

  function get_xuat_ngay($start_month, $end_month, $start_date, $end_date) {
    $query = db_select('dt_ban_hang_chi_tiet_hang_mua', 'hm');
    $query->fields('hm', array('id', 'ma_thanh_pham', 'gia_tien', 'thoi_gian'));
    $query->addExpression('SUM(hm.so_luong)', 'so_luong');
    $query->groupBy('hm.ma_thanh_pham');
    $query->groupBy('hm.gia_tien');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = hm.ma_thanh_pham');
    $query->fields('sp', array('ten_thanh_pham', 'don_vi'));
    $query->condition('hm.thoi_gian', $start_date, '>=');
    $query->condition('hm.thoi_gian', $end_date, '<=');
    $result = $query->orderBy('hm.ma_thanh_pham')->execute();
    $tong_so_luong = 0;
    $tong_tien = 0;
    $tong_den_thang = get_tong_xuat_den_thang($start_month, $start_date);
    $data = array();
    if ($start_date > $start_month + 86400) {
      $ngay = 'Ngày 1 - ' . date('d/m', $start_date - 86400);
    }
    else {
      $ngay = 'Ngày ' . date('d/m', $start_month);
    }
    $data[] = array(
      'data' => array(
        array('data' => 'Xuất', 'colspan' => 6, 'class' => array('text-center', 'text-bold')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data[] = array(
      'data' => array(
        '',
        'SP',
        'Bán',
        'KM',
        'BH',
        'T.tiền',
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    /*while ($rs = $result->fetchObject()) {
      $tong_so_luong += $rs->so_luong;
      $tong_tien += $rs->gia_tien * $rs->so_luong;
    }*/
    $record_lsp = get_tong_record_loai_san_pham();
    //từ đầu tháng đến ngày hôm nay
    $j = 0;
    $result1 = get_tong_xuat_km_hoi_den_thang($start_month, $start_date);
    while ($rs1 = $result1->fetchObject()) {
      if ($j == 0) {
        $data[] = array(
          //array('data' => $ngay, 'rowspan' => $record_lsp),
          array('data' => $ngay, 'rowspan' => $record_lsp),
          array('data' => $rs1->loai_san_pham, 'class' => array('text-bold')),
          array('data' => number_format($rs1->ban_thang)),
          array('data' => number_format($rs1->km_thang)),
          array('data' => number_format($rs1->bh_thang + $rs1->nbht_thang)),
          array('data' => number_format($tong_den_thang), 'rowspan' => $record_lsp),
        );
      }
      else {
        $data[] = array(
          //array('data' => $ngay, 'rowspan' => $record_lsp),
          array('data' => $rs1->loai_san_pham, 'class' => array('text-bold')),
          array('data' => number_format($rs1->ban_thang)),
          array('data' => number_format($rs1->km_thang)),
          array('data' => number_format($rs1->bh_thang + $rs1->nbht_thang)),
        );
      }
      $j++;
    }
    $result4 = get_sp_tong_xuat_km_hoi_den_thang($start_date, $end_date);
    while ($rs4 = $result->fetchObject()) {
      $tong_so_luong += $rs4->so_luong;
      $tong_tien += $rs4->gia_tien * $rs4->so_luong;
      $data[] = array(
        '',
        array('data' => $rs4->ten_thanh_pham),
        array('data' => number_format($rs4->so_luong)),
        array('data' => number_format(get_list_phieu_ban_hang(1,$rs4->ma_thanh_pham, $rs4->gia_tien, $start_date, $end_date))),
        array('data' => number_format(get_list_phieu_ban_hang(2,$rs4->ma_thanh_pham, $rs4->gia_tien, $start_date, $end_date))),
        array('data' => number_format($rs4->so_luong * $rs4->gia_tien)),
      );
      // }
    }
    //hôm nay
    $k = 0;
    $result2 = get_tong_xuat_km_hoi_den_thang($start_date, $end_date);
    while ($rs2 = $result2->fetchObject()) {
      if ($k == 0) {
        $data[] = array(
          //array('data' => $ngay, 'rowspan' => $record_lsp),
          array('data' => 'Tổng ' . date('d/m', $start_date), 'rowspan' => $record_lsp),
          array('data' => $rs2->loai_san_pham, 'class' => array('text-bold')),
          array('data' => number_format($rs2->ban_thang)),
          array('data' => number_format($rs2->km_thang)),
          array('data' => number_format($rs2->bh_thang + $rs2->nbht_thang)),
          array('data' => number_format($tong_tien), 'rowspan' => $record_lsp),
        );
      }
      else {
        $data[] = array(
          //array('data' => $ngay, 'rowspan' => $record_lsp),
          array('data' => $rs2->loai_san_pham, 'class' => array('text-bold')),
          array('data' => number_format($rs2->ban_thang)),
          array('data' => number_format($rs2->km_thang)),
          array('data' => number_format($rs2->bh_thang + $rs2->nbht_thang)),
        );
      }
      $k++;
    }
    //từ đầu tháng đến hết hôm nay
    $l = 0;
    $result3 = get_tong_xuat_km_hoi_den_thang($start_month, $end_date);
    while ($rs3 = $result3->fetchObject()) {
      if ($l == 0) {
        $data[] = array(
          //array('data' => $ngay, 'rowspan' => $record_lsp),
          array('data' => 'Tổng đến ngày ' . date('d/m', $start_date), 'rowspan' => $record_lsp),
          array('data' => $rs3->loai_san_pham, 'class' => array('text-bold')),
          array('data' => number_format($rs3->ban_thang)),
          array('data' => number_format($rs3->km_thang)),
          array('data' => number_format($rs3->bh_thang + $rs3->nbht_thang)),
          array('data' => number_format($tong_tien + $tong_den_thang), 'rowspan' => $record_lsp),
        );
      }
      else {
        $data[] = array(
          //array('data' => $ngay, 'rowspan' => $record_lsp),
          array('data' => $rs3->loai_san_pham, 'class' => array('text-bold')),
          array('data' => number_format($rs3->ban_thang)),
          array('data' => number_format($rs3->km_thang)),
          array('data' => number_format($rs3->bh_thang + $rs3->nbht_thang)),
        );
      }
      $l++;
    }
    /*$data[] = array(
      'data' => array(
        array('data' => 'Tổng ' . date('d/m', $start_date), 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($tong_so_luong), 'class' => array('text-bold')),
        '',
        array('data' => number_format($tong_tien), 'class' => array('text-bold')),
      ),
    );
    $data[] = array(
      'data' => array(
        array('data' => 'Tổng đến ngày ' . date('d/m', $start_date), 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
        '',
        array('data' => number_format($tong_tien + $tong_den_thang), 'class' => array('text-bold')),
      ),
    );*/
    return $data;
  }

  function get_nhap_ngay($start_month, $end_month, $start_date, $end_date) {
    $query = db_select('dt_vat_tu_phieu_nhap_chi_tiet', 'nvt');
    $query->fields('nvt', array('id', 'ma_vat_tu', 'gia_tien', 'thoi_gian'));
    $query->addExpression('SUM(nvt.so_luong)', 'so_luong');
    $query->groupBy('nvt.ma_vat_tu');
    $query->groupBy('nvt.gia_tien');
    $query->innerJoin('dt_vat_tu', 'vt', 'vt.ma_vat_tu = nvt.ma_vat_tu');
    $query->fields('vt', array('ten_vat_tu'));
    $query->condition('nvt.thoi_gian', $start_date, '>=');
    $query->condition('nvt.thoi_gian', $end_date, '<=');
    $result = $query->orderBy('nvt.ma_vat_tu')->execute();
    $tong_so_luong = 0;
    $tong_tien = 0;
    $tong_den_thang = get_tong_nhap_den_thang($start_month, $start_date);
    $data = array();
    if ($start_date > $start_month + 86400) {
      $ngay = 'Ngày 1 - ' . date('d/m', $start_date - 86400);
    }
    else {
      $ngay = 'Ngày ' . date('d/m', $start_month);
    }
    $data[] = array(
      'data' => array(
        array('data' => 'Nhập', 'colspan' => 4, 'class' => array('text-center', 'text-bold')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data[] = array(
      'data' => array(
        array('data' => $ngay, 'colspan' => 3, 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($tong_den_thang), 'class' => array('text-center', 'text-bold')),
      ),
    );
    $data[] = array(
      'data' => array(
        '',
        'Tên vật tư',
        'Số lượng',
        'Thành tiền',
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $record_lsp = get_tong_record_loai_san_pham();
    //từ đầu tháng đến hết ngày hôm qua
    $result1 = get_tong_hoi_den_thang($start_month, $start_date);
    $j = 0;
    while ($rs1 = $result1->fetchObject()) {
      if ($j == 0) {
        $data[] = array(
          array('data' => $ngay, 'rowspan' => $record_lsp),
          array('data' => $rs1->loai_san_pham . ' hồi', 'class' => array('text-bold')),
          'so_luong' => number_format($rs1->hoi_thang),
          'gia_tien' => '',
        );
      }
      else {
        $data[] = array(
          array('data' => $rs1->loai_san_pham . ' hồi', 'class' => array('text-bold')),
          'so_luong' => number_format($rs1->hoi_thang),
          'gia_tien' => '',
        );
      }
      $j++;
    }
    while ($rs = $result->fetchObject()) {
      $tong_so_luong += $rs->so_luong;
      $tong_tien += $rs->gia_tien * $rs->so_luong;
      $data[] = array(
        // array('data' => 'từ ngày đến ngày', 'colspan' => $record_lsp),
        '',
        'ten_thanh_pham' => $rs->ten_vat_tu,
        'so_luong' => number_format($rs->so_luong),
        'gia_tien' => number_format($rs->gia_tien * $rs->so_luong),
      );
    }
    //Trong ngày hôm nay
    $k = 0;
    $result2 = get_tong_hoi_den_thang($start_date, $end_date);
    while ($rs2 = $result2->fetchObject()) {
      if ($k == 0) {
        $data[] = array(
          array('data' => 'Tổng ' . date('d/m', $start_date), 'rowspan' => $record_lsp),
          array('data' => $rs2->loai_san_pham . ' hồi', 'class' => array('text-bold')),
          'so_luong' => number_format($rs2->hoi_thang),
          'gia_tien' => '',
        );
      }
      else {
        $data[] = array(
          array('data' => $rs2->loai_san_pham . ' hồi', 'class' => array('text-bold')),
          'so_luong' => number_format($rs2->hoi_thang),
          'gia_tien' => '',
        );
      }
      $k++;
    }
    //Hết ngày hôm nay
    $l = 0;
    $result3 = get_tong_hoi_den_thang($start_month, $end_date);
    while ($rs3 = $result3->fetchObject()) {
      if ($l == 0) {
        $data[] = array(
          array('data' => 'Tổng đến ngày ' . date('d/m', $start_date), 'rowspan' => $record_lsp),
          array('data' => $rs3->loai_san_pham . ' hồi', 'class' => array('text-bold')),
          'so_luong' => number_format($rs3->hoi_thang),
          'gia_tien' => '',
        );
      }
      else {
        $data[] = array(
          array('data' => $rs3->loai_san_pham . ' hồi', 'class' => array('text-bold')),
          'so_luong' => number_format($rs3->hoi_thang),
          'gia_tien' => '',
        );
      }
      $l++;
    }
    $data[] = array(
      'data' => array(
        array('data' => 'Tổng ' . date('d/m', $start_date), 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($tong_so_luong), 'class' => array('text-bold')),
        array('data' => number_format($tong_tien), 'class' => array('text-bold')),
      ),
    );
    $data[] = array(
      'data' => array(
        array('data' => 'Tổng đến ngày ' . date('d/m', $start_date), 'colspan' => 3, 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($tong_tien + $tong_den_thang), 'class' => array('text-bold')),
      ),
    );
    return $data;
  }

  function get_thu_ngay($start_month, $end_month, $start_date, $end_date, $type) {
    $query_x = db_select('dt_phieu_thu_le', 'tl');
    $query_x->fields('tl', array('tong_tien', 'thoi_gian', 'nguoi_nop'));
    $query_x->addField('tl', 'ghi_chu', 'type');
    // $query_x->addExpression("'Thu lẻ'", 'type');
    $query_x->condition('tl.thoi_gian', $start_date, '>=');
    $query_x->condition('tl.thoi_gian', $end_date, '<=');
    $query_n = db_select('dt_phieu_thu_thanh_pham', 'ttp');
    $query_n->fields('ttp', array('tong_tien', 'thoi_gian'));
    $query_n->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = ttp.ma_khach_hang');
    $query_n->addField('kh', 'ten_khach_hang', 'nguoi_nop');
    $query_n->addExpression("'Thu tiền hàng'", 'type');
    $query_n->condition('ttp.thoi_gian', $start_date, '>=');
    $query_n->condition('ttp.thoi_gian', $end_date, '<=');
    $query_x->union($query_n, 'UNION ALL');
    $query = db_select($query_x, 'vt');
    $query->fields('vt', array('tong_tien', 'thoi_gian', 'nguoi_nop', 'type'));
    $result = $query->execute();
    $tong_tien = 0;
    $tong_den_thang = get_tong_thu_den_thang($start_month, $start_date);
    $data = array();
    if ($start_date > $start_month + 86400) {
      $ngay = 'Ngày 1 - ' . date('d/m', $start_date - 86400);
    }
    else {
      $ngay = 'Ngày ' . date('d/m', $start_month);
    }
    $data[] = array(
      'data' => array(
        array('data' => 'Thu', 'colspan' => 4, 'class' => array('text-center', 'text-bold')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data[] = array(
      'data' => array(
        array('data' => $ngay, 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($tong_den_thang), 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
      ),
    );
    while ($rs = $result->fetchObject()) {
      $tong_tien += $rs->tong_tien;
      $data[] = array(
        'nguoi_nop' => $rs->nguoi_nop,
        'so_tien' => number_format($rs->tong_tien),
        array('data' => $rs->type, 'colspan' => 2),
      );
    }
    $data[] = array(
      'data' => array(
        array('data' => 'Tổng ' . date('d/m', $start_date), 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($tong_tien), 'class' => array('text-bold')),
        '',
        '',
      ),
    );
    $data[] = array(
      'data' => array(
        array('data' => 'Tổng đến ngày ' . date('d/m', $start_date), 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($tong_tien + $tong_den_thang), 'class' => array('text-bold')),
        '',
        '',
      ),
    );
    if ($type == 1) {
      return $data;
    }
    else {
      return $tong_tien;
    }
  }

  function get_chi_ngay($start_month, $end_month, $start_date, $end_date, $type) {
    $query_x = db_select('dt_phieu_chi_le', 'cl');
    $query_x->fields('cl', array('tong_tien', 'thoi_gian', 'nguoi_nhan'));
    $query_x->addField('cl', 'ghi_chu', 'type');
    //$query_x->addExpression("'Chi lẻ'", 'type');
    $query_x->condition('cl.thoi_gian', $start_date, '>=');
    $query_x->condition('cl.thoi_gian', $end_date, '<=');
    $query_n = db_select('dt_phieu_chi_vat_tu', 'cvt');
    $query_n->fields('cvt', array('tong_tien', 'thoi_gian'));
    $query_n->innerJoin('dt_nha_cung_cap', 'ncc', 'ncc.ma_nha_cung_cap = cvt.ma_nha_cung_cap');
    $query_n->addField('ncc', 'ten_nha_cung_cap', 'nguoi_nhan');
    $query_n->addExpression("'Thanh toán tiền hàng'", 'type');
    $query_n->condition('cvt.thoi_gian', $start_date, '>=');
    $query_n->condition('cvt.thoi_gian', $end_date, '<=');
    $query_x->union($query_n, 'UNION ALL');
    $query = db_select($query_x, 'vt');
    $query->fields('vt', array('tong_tien', 'thoi_gian', 'nguoi_nhan', 'type'));
    $result = $query->execute();
    $tong_tien = 0;
    $tong_den_thang = get_tong_chi_den_thang($start_month, $start_date);
    $data = array();
    if ($start_date > $start_month + 86400) {
      $ngay = 'Ngày 1 - ' . date('d/m', $start_date - 86400);
    }
    else {
      $ngay = 'Ngày ' . date('d/m', $start_month);
    }
    $data[] = array(
      'data' => array(
        array('data' => 'Chi', 'colspan' => 4, 'class' => array('text-center', 'text-bold')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data[] = array(
      'data' => array(
        array('data' => $ngay, 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($tong_den_thang), 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
      ),
    );
    while ($rs = $result->fetchObject()) {
      $tong_tien += $rs->tong_tien;
      $data[] = array(
        'nguoi_nop' => $rs->nguoi_nhan,
        'so_tien' => number_format($rs->tong_tien),
        array('data' => $rs->type, 'colspan' => 2),
      );
    }
    $data[] = array(
      'data' => array(
        array('data' => 'Tổng ' . date('d/m', $start_date), 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($tong_tien), 'class' => array('text-bold')),
        '',
        '',
      ),
    );
    $data[] = array(
      'data' => array(
        array('data' => 'Tổng chi đến ngày ' . date('d/m', $start_date), 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($tong_tien + $tong_den_thang), 'class' => array('text-bold')),
        '',
        '',
      ),
    );
    if ($type == 1) {
      return $data;
    }
    else {
      return $tong_tien;
    }
  }

  function get_tong_nhap_den_thang($start_month, $start_date) {
    $query = db_select('dt_vat_tu_phieu_nhap_chi_tiet', 'nvt');
    $query->fields('nvt', array('id', 'ma_vat_tu', 'gia_tien', 'so_luong', 'thoi_gian'));
    $query->condition('nvt.thoi_gian', $start_month, '>=');
    $query->condition('nvt.thoi_gian', $start_date, '<');
    $result = $query->execute();
    $tong_tien = 0;
    while ($rs = $result->fetchObject()) {
      $tong_tien += $rs->gia_tien * $rs->so_luong;
    }
    return $tong_tien;
  }

  function get_tong_hoi_den_thang($start_month, $start_date) {
    $query = db_select('dt_loai_san_pham', 'lsp');
    $query->fields('lsp', array('id', 'loai_san_pham'));
    $query->addExpression('(SELECT SUM(x1.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS x1 ,{dt_san_pham} AS sp  WHERE lsp.id = sp.idloai and sp.ma_thanh_pham = x1.ma_thanh_pham AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'hoi_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $start_date));
    $result = $query->orderBy('lsp.id')->execute();
    return $result;
  }

  function get_tong_xuat_km_hoi_den_thang($start_month, $start_date) {
    $query = db_select('dt_loai_san_pham', 'lsp');
    $query->fields('lsp', array('id', 'loai_san_pham'));
    //$query->addExpression('(SELECT SUM(x1.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS x1 ,{dt_san_pham} AS sp  WHERE lsp.id = sp.idloai and sp.ma_thanh_pham = x1.ma_thanh_pham and x1.don_gia_tru_tien =0 AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'hoi_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $start_date));
    $query->addExpression('(SELECT SUM(n1.so_luong) FROM {dt_ban_hang_chi_tiet_hang_mua} AS n1, {dt_san_pham} AS sp  WHERE lsp.id = sp.idloai and sp.ma_thanh_pham = n1.ma_thanh_pham AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'ban_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $start_date));
    $query->addExpression('(SELECT SUM(k1.so_luong) FROM {dt_ban_hang_chi_tiet_khuyen_mai} AS k1,{dt_san_pham} AS sp WHERE lsp.id = sp.idloai and sp.ma_thanh_pham = k1.ma_thanh_pham and k1.tru_tien=0 AND k1.thoi_gian >= :k1_start_month AND k1.thoi_gian <= :k1_end_month)', 'km_thang', array(':k1_start_month' => $start_month, ':k1_end_month' => $start_date));
    $query->addExpression('(SELECT SUM(b1.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS b1 ,{dt_san_pham_hoi} AS h1,{dt_san_pham} AS sp WHERE lsp.id = sp.idloai and b1.ma_phieu_hang_hoi = h1.ma_phieu_hang_hoi and h1.trang_thai_bao_hanh =1 and b1.don_gia_tru_tien = 0 and sp.ma_thanh_pham = b1.ma_thanh_pham AND b1.thoi_gian_tra_bh <> 0 AND b1.thoi_gian_tra_bh >= :b1_start_month AND b1.thoi_gian_tra_bh <= :b1_end_month)', 'bh_thang', array(':b1_start_month' => $start_month, ':b1_end_month' => $start_date));
    $query->addExpression('(SELECT SUM(nbht1.so_no) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nbht1,{dt_san_pham} AS sp WHERE lsp.id = sp.idloai and sp.ma_thanh_pham = nbht1.ma_thanh_pham AND nbht1.thoi_gian >= :nbht1_start_month AND nbht1.thoi_gian <= :nbht1_end_month)', 'nbht_thang', array(':nbht1_start_month' => $start_month, ':nbht1_end_month' => $start_date));
    $result = $query->orderBy('lsp.id')->execute();
    return $result;
  }

  function get_sp_tong_xuat_km_hoi_den_thang($start_month, $start_date) {
    $query = db_select('dt_san_pham', 'sp');
    $query->fields('sp', array('id', 'ma_thanh_pham', 'ten_thanh_pham', 'thoi_gian'));
    //$query->addExpression('(SELECT SUM(x1.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS x1 ,{dt_san_pham} AS sp  WHERE lsp.id = sp.idloai and sp.ma_thanh_pham = x1.ma_thanh_pham and x1.don_gia_tru_tien =0 AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'hoi_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $start_date));
    $query->addExpression('(SELECT SUM(n1.so_luong) FROM {dt_ban_hang_chi_tiet_hang_mua} AS n1  WHERE sp.ma_thanh_pham = n1.ma_thanh_pham AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'ban_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $start_date));
    $query->addExpression('(SELECT SUM(k1.so_luong) FROM {dt_ban_hang_chi_tiet_khuyen_mai} AS k1 WHERE sp.ma_thanh_pham = k1.ma_thanh_pham and k1.tru_tien=0 AND k1.thoi_gian >= :k1_start_month AND k1.thoi_gian <= :k1_end_month)', 'km_thang', array(':k1_start_month' => $start_month, ':k1_end_month' => $start_date));
    $query->addExpression('(SELECT SUM(b1.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS b1 ,{dt_san_pham_hoi} AS h1 WHERE b1.ma_phieu_hang_hoi = h1.ma_phieu_hang_hoi and h1.trang_thai_bao_hanh =1 and b1.don_gia_tru_tien = 0 and sp.ma_thanh_pham = b1.ma_thanh_pham AND b1.thoi_gian >= :b1_start_month AND b1.thoi_gian <= :b1_end_month)', 'bh_thang', array(':b1_start_month' => $start_month, ':b1_end_month' => $start_date));
    $query->addExpression('(SELECT SUM(nbht1.so_no) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nbht1 WHERE sp.ma_thanh_pham = nbht1.ma_thanh_pham AND nbht1.thoi_gian >= :nbht1_start_month AND nbht1.thoi_gian <= :nbht1_end_month)', 'nbht_thang', array(':nbht1_start_month' => $start_month, ':nbht1_end_month' => $start_date));
    $result = $query->orderBy('sp.ma_thanh_pham')->execute();
    return $result;
  }

  function get_list_phieu_ban_hang($type, $ma_thanh_pham, $gia_tien, $startdate, $enddate) {
    $query = db_select('dt_ban_hang_chi_tiet_hang_mua', 'bh');
    $query->fields('bh', array('ma_phieu_ban_hang', 'thoi_gian'));
    $query->condition('bh.thoi_gian', $startdate, '>=');
    $query->condition('bh.thoi_gian', $enddate, '<=');
    $query->condition('bh.ma_thanh_pham', $ma_thanh_pham, '=');
    $query->condition('bh.gia_tien', $gia_tien, '=');
    //$query->groupBy('bh.ma_phieu_ban_hang');
    $result = $query->execute();
    $tong_khuyen_mai = 0;
    if ($type == 1) {
      while ($rs = $result->fetchObject()) {
        $tong_khuyen_mai += get_tong_km_theo_pbh($ma_thanh_pham, $rs->ma_phieu_ban_hang);
      }
    }
    else {
      while ($rs = $result->fetchObject()) {
        $tong_khuyen_mai += get_tong_bh_theo_pbh($ma_thanh_pham, $rs->ma_phieu_ban_hang);
      }
    }
    return $tong_khuyen_mai;
  }

  function get_tong_km_theo_pbh($ma_thanh_pham, $ma_pbh) {
    $query = db_select('dt_ban_hang_chi_tiet_khuyen_mai', 'km');
    $query->fields('km', array('id', 'ma_thanh_pham', 'thoi_gian', 'so_luong'));
    $query->condition('km.ma_phieu_ban_hang', $ma_pbh, '=');
    $query->condition('km.tru_tien', 0, '=');
    $query->condition('km.ma_thanh_pham', $ma_thanh_pham, '=');
    $result = $query->execute();
    $tong_km = 0;
    while ($rs = $result->fetchObject()) {
      $tong_km += $rs->so_luong;
      // drupal_set_message($rs->so_luong.'###'.$tong_km,'error');
    }
    //drupal_set_message('###'.$tong_km,'error');
    return $tong_km;
  }

  function get_tong_bh_theo_pbh($ma_thanh_pham, $ma_pbh) {
    $query = db_select('dt_ban_hang_chi_tiet_duoc_bao_hanh', 'p');
    $query->innerJoin('dt_san_pham_hoi_chi_tiet', 'ct', 'p.id_chi_tiet_hoi = ct.id');
    $query->innerJoin('dt_san_pham_hoi', 'ph', 'ph.ma_phieu_hang_hoi = ct.ma_phieu_hang_hoi');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_phieu_hang_hoi', 'ma_thanh_pham', 'so_luong', 'so_luong_hoi', 'don_gia_tru_tien'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->fields('ph', array('ghi_chu'));
    $query->condition('p.ma_phieu_ban_hang', $ma_pbh);
    $query->condition('ct.ma_thanh_pham', $ma_thanh_pham);
    $query->condition('ph.trang_thai_bao_hanh', 1, '=');
    $query->condition('ct.don_gia_tru_tien', 0, '=');
    //h1.trang_thai_bao_hanh =1 and b1.don_gia_tru_tien = 0
    $result = $query->execute();
    $tong_km = 0;
    while ($rs = $result->fetchObject()) {
      $tong_km += $rs->so_luong;
      // drupal_set_message($rs->so_luong.'###'.$tong_km,'error');
    }
    //drupal_set_message('###'.$tong_km,'error');
    return $tong_km;
  }

  function get_tong_record_loai_san_pham() {
    $query = db_select('dt_loai_san_pham', 'lsp');
    $query->fields('lsp', array('id', 'loai_san_pham'));
    $result = $query->execute();
    $i = 0;
    while ($rs = $result->fetchObject()) {
      $i++;
    }
    return $i;
  }

  function get_tong_thu_den_thang($start_month, $start_date) {
    $query_x = db_select('dt_phieu_thu_le', 'tl');
    $query_x->fields('tl', array('tong_tien', 'thoi_gian', 'ghi_chu', 'nguoi_nop'));
    $query_x->condition('tl.thoi_gian', $start_month, '>=');
    $query_x->condition('tl.thoi_gian', $start_date, '<');
    $result_x = $query_x->execute();
    $tong_tien = 0;
    while ($rs_x = $result_x->fetchObject()) {
      $tong_tien += $rs_x->tong_tien;
    }
    $query_n = db_select('dt_phieu_thu_thanh_pham', 'ttp');
    $query_n->fields('ttp', array('tong_tien', 'thoi_gian', 'ghi_chu'));
    $query_n->condition('ttp.thoi_gian', $start_month, '>=');
    $query_n->condition('ttp.thoi_gian', $start_date, '<');
    $result_n = $query_n->execute();
    while ($rs_n = $result_n->fetchObject()) {
      $tong_tien += $rs_n->tong_tien;
    }
    return $tong_tien;
  }

  function get_tong_chi_den_thang($start_month, $start_date) {
    $query_x = db_select('dt_phieu_chi_le', 'cl');
    $query_x->fields('cl', array('tong_tien', 'thoi_gian', 'ghi_chu', 'nguoi_nhan'));
    $query_x->condition('cl.thoi_gian', $start_month, '>=');
    $query_x->condition('cl.thoi_gian', $start_date, '<');
    $result_x = $query_x->execute();
    $tong_tien = 0;
    while ($rs_x = $result_x->fetchObject()) {
      $tong_tien += $rs_x->tong_tien;
    }
    $query_n = db_select('dt_phieu_chi_vat_tu', 'cvt');
    $query_n->fields('cvt', array('tong_tien', 'thoi_gian', 'ghi_chu'));
    $query_n->condition('cvt.thoi_gian', $start_month, '>=');
    $query_n->condition('cvt.thoi_gian', $start_date, '<');
    $result_n = $query_n->execute();
    while ($rs_n = $result_n->fetchObject()) {
      $tong_tien += $rs_n->tong_tien;
    }
    return $tong_tien;
  }

  function get_tong_xuat_den_thang($start_month, $start_date) {
    $query = db_select('dt_ban_hang_chi_tiet_hang_mua', 'hm');
    $query->fields('hm', array('id', 'ma_thanh_pham', 'gia_tien', 'so_luong', 'thoi_gian'));
    $query->condition('hm.thoi_gian', $start_month, '>=');
    $query->condition('hm.thoi_gian', $start_date, '<');
    $result = $query->execute();
    $tong_tien = 0;
    while ($rs = $result->fetchObject()) {
      $tong_tien += $rs->gia_tien * $rs->so_luong;
    }
    return $tong_tien;
  }

  function get_tong_quy_den_thang($start_month) {
    $query = db_select('dt_thu_quy', 'tq');
    $query->fields('tq', array('id', 'ma_thu_quy', 'ten_thu_quy', 'thoi_gian'));
    $query->addExpression('(SELECT SUM(n0.tong_tien) FROM {dt_giao_tien} AS n0 WHERE tq.ma_thu_quy = n0.ma_thu_quy_nhan AND n0.thoi_gian < :n0_start_month)', 'nhan_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(tsp0.tong_tien) FROM {dt_phieu_thu_thanh_pham} AS tsp0 WHERE tq.ma_thu_quy = tsp0.ma_thu_quy AND tsp0.thoi_gian < :tsp0_start_month)', 'tsp0_den_thang', array(':tsp0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(tl0.tong_tien) FROM {dt_phieu_thu_le} AS tl0 WHERE tq.ma_thu_quy = tl0.ma_thu_quy AND tl0.thoi_gian < :tl0_start_month)', 'tl0_den_thang', array(':tl0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(cvt0.tong_tien) FROM {dt_phieu_chi_vat_tu} AS cvt0 WHERE tq.ma_thu_quy = cvt0.ma_thu_quy AND cvt0.thoi_gian < :cvt0_start_month)', 'cvt0_den_thang', array(':cvt0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(cl0.tong_tien) FROM {dt_phieu_chi_le} AS cl0 WHERE tq.ma_thu_quy = cl0.ma_thu_quy AND cl0.thoi_gian < :cl0_start_month)', 'cl0_den_thang', array(':cl0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(c0.tong_tien) FROM {dt_giao_tien} AS c0 WHERE tq.ma_thu_quy = c0.ma_thu_quy_chi AND c0.thoi_gian < :c0_start_month)', 'chi_den_thang', array(':c0_start_month' => $start_month));
    $query->addExpression('(SELECT d.tong_tien FROM {dt_thu_quy_du_lieu} AS d WHERE tq.ma_thu_quy = d.ma_thu_quy AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT tr0.tong_tien FROM {dt_phieu_trich_quy} AS tr0 WHERE tq.ma_thu_quy = tr0.ma_thu_quy AND tr0.thoi_gian < :tr0_start_month)', 'trich_den_thang', array(':tr0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(bht.tong_tien) FROM {dt_phieu_chuyen_tien_hang} AS bht WHERE tq.ma_thu_quy = bht.ma_thu_quy_nhan AND bht.thoi_gian < :bht_start_month)', 'thu_ban_hang_den_thang', array(':bht_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(bhc.tong_tien) FROM {dt_phieu_chuyen_tien_hang} AS bhc WHERE tq.ma_thu_quy = bhc.ma_nhan_vien_ban_hang AND bhc.thoi_gian < :bhc_start_month)', 'chi_ban_hang_den_thang', array(':bhc_start_month' => $start_month));
    $result = $query->orderBy('tq.ma_thu_quy')->execute();
    $tong_tien = 0;
    while ($rs = $result->fetchObject()) {
      $tong_tien += $rs->thu_ban_hang_den_thang + $rs->nhan_den_thang + $rs->tsp0_den_thang + $rs->tl0_den_thang - $rs->chi_ban_hang_den_thang - $rs->cvt0_den_thang - $rs->cl0_den_thang - $rs->chi_den_thang + $rs->du - $rs->trich_den_thang;
    }
    return $tong_tien;
  }

    function get_thanh_pham_bieu_do($time){
        $query = db_select('dt_san_pham', 'p');
        $query->fields('p', array('id','ten_thanh_pham', 'so_luong', 'gia_tien'));
    $query->condition('p.id', $time, '<');
        $result = $query->execute();
        $data = array();
        while ($rs = $result->fetchObject()) {
        $data[] = $rs;
      }
        return $data;
    }

